package com.floreantpos.model.dao;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;

import com.floreantpos.model.Store;
import com.floreantpos.model.Users;
import com.floreantpos.util.Util;

public class StoreDAO extends BaseStoreDAO implements
		com.floreantpos.model.dao.iface.StoreDAO {

	public StoreDAO() {
	}

	public List findAllGroupByName() {
		String hql = "select store.Name from Store store group by store.Name";
		Query query = createNewSession().createQuery(hql);
		return query.list();
	}

	public List<Store> findAllByToday() {
		return createNewSession()
				.createCriteria(Store.class)
				.add(Restrictions.le(
						Store.PROP_MODIFIED_TIME,
						Util.parseDateTime(Util.formatDate(new Date())
								+ " 23:59:59")))
				.add(Restrictions.ge(
						Store.PROP_MODIFIED_TIME,
						Util.parseDateTime(Util.formatDate(new Date())
								+ " 00:00:00"))).list();
	}

	public List findAllStoreByToday() {
		String today = Util.formatDate(new Date());
		String sql = " select a.id, a.name, a.numbers as newNum, b.numbers as oldNum, c.total_sales, c.loss_number, d.numbers as rest_number, e.type"
				+ " from store a"
				+ " left outer join store b"
				+ " on a.name = b.name and b.type is not null and date(a.modified_time) = date(b.modified_time)"
				+ " left outer join ("
				+ " select a.store_name, sum(a.number), sum(b.count), sum(a.number)*sum(b.count) total_sales, sum(c.numbers) loss_number"
				+ " from store_item_rel a"
				+ " left outer join ("
				+ " select sum(item_count) count, item_id"
				+ " from ticket_item"
				+ " where date(modified_time) = '"
				+ today
				+ "'"
				+ " group by item_id"
				+ " ) b"
				+ " on a.item_id = b.item_id"
				+ " left outer join store_loss c"
				+ " on a.store_name = c.store_name"
				+ " group by a.store_name) c"
				+ " on a.name = c.store_name"
				+ " left outer join store_rest d"
				+ " on d.store_name = a.name and date(d.modified_time) = '"
				+ today
				+ "'"
				+ " left outer join store e"
				+ " on INTEGER(e.type) = a.id and e.type is not null"
				+ " where a.type is null and date(a.modified_time) = '"
				+ today
				+ "'";

		Query query = createNewSession().createSQLQuery(sql);
		return query.list();
	}

	public void addStoreForward(String storeName, int numbers, Users user,
			Integer type, String modifiedTime) {

		if (modifiedTime.equalsIgnoreCase("today"))
			modifiedTime = Util.formatDate(new Date());

		Store store = new Store();
		store.setNumbers(numbers);
		store.setName(storeName);
		store.setUser(user);
		store.setType("" + type);
		store.setModifiedTime(Util.parseDate(Util.getDate(modifiedTime, 1)));
		this.save(store);
	}
}